package dao.mysql;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.PriorityDTO;
import model.UserDTO;
import dao.DAOException;
import dao.PriorityDAO;
public class PriorityDAOImpl implements PriorityDAO{
	
	private static final String dbUrl = "jdbc:mysql://localhost:3306/facilitybookingsystem";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";
	
	public void insertPriority (PriorityDTO priority) throws DAOException
	{
		String insertSql = "INSERT INTO BookingType (PriorityName) VALUES('" + priority.getPriorityName() +"');";
				try {
					Class.forName("com.mysql.jdbc.Driver");
				} catch (ClassNotFoundException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				Connection conn = null;
				try {
					conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
					Statement st = conn.createStatement();
					Logger.getLogger(getClass().getName()).log(Level.INFO,
							"Executing insert: " + insertSql);
					st.executeUpdate(insertSql);
					st.close();
				} catch (Exception e) {
					String error = "Error selecting users. Nested Exception is: " + e;
					Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
					throw new DAOException(error);
				} finally {
					try {
						conn.rollback();
						conn.close();
					} catch (Exception e) {
					}
				}	
	}
	
	public void deletePriority(PriorityDTO priority)throws DAOException {
		String deleteSql = "DELETE FROM bookingtype WHERE PriorityID="
				+ priority.getPriorityID() + ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing delete: " + deleteSql);
			st.executeUpdate(deleteSql);
			st.close();
		} catch (Exception e) {
			String error = "Error deleting users. Nested Exception is: " + e;
			System.out.println("#$#$#@$#@#@$#@$#@$#$#@");
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
}
	
	public ArrayList<PriorityDTO> findAllPriorities()throws DAOException{
		ArrayList<PriorityDTO> priority = new ArrayList<PriorityDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT * FROM facilitybookingsystem.BookingType;";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				PriorityDTO onePriority = new PriorityDTO();
				onePriority.setPriorityID(rs.getInt("PriorityID"));
				onePriority.setPriorityName(rs.getString("Priorityname"));			
				
				priority.add(onePriority);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return priority;
	}
	
	public PriorityDTO findPriority(int priorityID) throws DAOException {
		String selectSql = "SELECT * FROM BookingType WHERE PriorityID=" + priorityID
				+ ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PriorityDTO onePriority = new PriorityDTO();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				onePriority.setPriorityID(rs.getInt("PriorityID"));
				onePriority.setPriorityName(rs.getString("PriorityName"));
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return onePriority;
	}
	
	public ArrayList<PriorityDTO> findAllPriorityNames()throws DAOException{
		ArrayList<PriorityDTO> priority = new ArrayList<PriorityDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT PriorityName FROM facilitybookingsystem.BookingType;";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				PriorityDTO onePriority = new PriorityDTO();
				onePriority.setPriorityName(rs.getString("Priorityname"));			
				
				priority.add(onePriority);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return priority;
	}
	
	

}
